/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package dao;

import dto.RoleDTO;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.logging.Level;
import java.util.logging.Logger;
import util.DBConfig;

/**
 *
 * @author vodangbinhtay
 */
public class RoleDAO {

    DBConfig conn;
    Connection connect;
    ArrayList<RoleDTO> list;

    String SQL_CREATE = "insert into Roles values(?,?)";
    String SQL_READALL = "Select * from Roles";
    String SQL_READ_BYID = "select * from Roles where RoleID = ?";
    String SQL_READ_BYNAME = "select * from Roles where Name=?";
    String SQL_UPDATE = "update Roles set Name=?, Description=? where RoleID=?";
    String SQL_DELETE = "delete Roles where RoleID=?";

    public RoleDAO() {
        conn = new DBConfig();
        list = new ArrayList<RoleDTO>();
    }
    public int create(RoleDTO dto) {
        try {
            
            connect = conn.OpenConnect();
            PreparedStatement pst = connect.prepareStatement(SQL_CREATE);
            pst.setString(1, dto.getRoleName());
            pst.setString(2, dto.getDescription());
            return pst.executeUpdate();
        } catch (SQLException ex) {
            Logger.getLogger(RoleDAO.class.getName()).log(Level.SEVERE, null, ex);
            return -1;
        }
    }

    public ArrayList<RoleDTO> readAll() {
        try {
            list = new ArrayList<RoleDTO>();
            connect = conn.OpenConnect();
            PreparedStatement pst = connect.prepareStatement(SQL_READALL);
            ResultSet rs = pst.executeQuery();
            if (rs == null) {
                return null;
            } else {
                while (rs.next()) {
                    RoleDTO dto = new RoleDTO();
                    dto.setRoleID(rs.getInt(0));
                    dto.setRoleName(rs.getString(1));
                    dto.setDescription(rs.getString(2));
                    list.add(dto);
                }
                return list;
            }

        } catch (SQLException ex) {
            Logger.getLogger(RoleDAO.class.getName()).log(Level.SEVERE, null, ex);
            return null;
        }
    }

    public RoleDTO readByID(int roleID) {
        try {
            connect = conn.OpenConnect();
            PreparedStatement pst = connect.prepareStatement(SQL_READ_BYID);
            pst.setInt(1, roleID);
            ResultSet rs = pst.executeQuery();
            if (rs == null) {
                return null;
            } else {
                while (rs.next()) {
                    RoleDTO dto = new RoleDTO();
                    dto.setRoleID(rs.getInt(1));
                    dto.setRoleName(rs.getString(2));
                    dto.setDescription(rs.getString(3));
                    return dto;
                }
                return null;
            }
        } catch (SQLException ex) {
            Logger.getLogger(RoleDAO.class.getName()).log(Level.SEVERE, null, ex);
            return null;
        }
    }

    public RoleDTO readByName(String roleName) {
        try {
            connect = conn.OpenConnect();
            PreparedStatement pst = connect.prepareStatement(SQL_READ_BYNAME);
            pst.setString(2, roleName);
            ResultSet rs = pst.executeQuery();
            if (rs == null) {
                return null;
            } else {
                while (rs.next()) {
                    RoleDTO dto = new RoleDTO();
                    dto.setRoleID(rs.getInt(1));
                    dto.setRoleName(rs.getString(2));
                    dto.setDescription(rs.getString(3));
                    return dto;
                }
                return null;
            }
        } catch (SQLException ex) {
            Logger.getLogger(RoleDAO.class.getName()).log(Level.SEVERE, null, ex);
            return null;
        }
    }

    public int update(RoleDTO dto) {
        try {
            connect = conn.OpenConnect();
            PreparedStatement pst = connect.prepareStatement(SQL_UPDATE);
            pst.setString(1, dto.getRoleName());
            pst.setString(2, dto.getDescription());
            pst.setInt(3, dto.getRoleID());
            return pst.executeUpdate();
        } catch (SQLException ex) {
            Logger.getLogger(RoleDAO.class.getName()).log(Level.SEVERE, null, ex);
            return -1;
        }
    }

    public int delete(int id) {
        try {
            connect = conn.OpenConnect();
            PreparedStatement pst = connect.prepareStatement(SQL_DELETE);
            pst.setInt(1,id);
            return pst.executeUpdate();
        } catch (SQLException ex) {
            Logger.getLogger(RoleDAO.class.getName()).log(Level.SEVERE, null, ex);
            return -1;
        }
    }
    public static void main(String[] args) {
        RoleDAO dao = new RoleDAO();
//        dao.create(new RoleDTO(1, "admin", "Admin"));
        //dao.update(new RoleDTO(1, "admin22", "Admin11"));
        //RoleDTO role= dao.readByID(1);
        int r = dao.delete(2);
    }
}
